/**
 * The DBManager class is the database worker module
 *
 * @author Ady Kakrania
 * @version 4/12/2007
 */

package com.elderhealth.util;

import java.sql.*;
import java.util.Properties;
import com.elderhealth.client.Client;
import com.elderhealth.worker.HomecareWorker;
import com.elderhealth.schedule.Schedule;
import com.elderhealth.schedule.Timeslot;
import com.elderhealth.schedule.Appointment;

public class DBManager
{
    /* instance variables */
    private final Properties properties;
    private Connection dbConnection;
    private String strConn;
    private Log logFile;
    private static DBManager instance;

    /**
     * Constructor for objects of class HomecareWorker
     */
    public DBManager()
    {
      /* Set up the log instance and check if logging is enabled */
      logFile = Log.getInstance();

      /* Set up the db connection using a properties file */
      properties = ElderhealthProperties.getInstance();
      try
      {
        /* Get connection variables from properties file */
        String user = properties.getProperty("JDBC_USERNAME");
        String passwd = properties.getProperty("JDBC_PASSWORD");
        String connection = properties.getProperty("JDBC_CONNECTION");

        /* Setup the connection string */
        strConn = connection + "?user=" + user + "&password=" + passwd;

        /* Open the database */
        openDB();

      }
      catch (Exception e)
      {
        /* connection string hardcoded and unencrypted in case of error only */
        System.out.println("DBManager: Constructor: Could not lookup " +
            "properties file. Check output/log for errors. Using default.");
        strConn = "jdbc:mysql://localhost/elderhealth?user=root&" +
            "password=elderhealth";
      }
    }

  /**
     * Returns an instance of the manager
     */
    public static DBManager getInstance()
    {
      /* Set up the log instance and check if logging is enabled */
      if (null == instance)
        instance = new DBManager();

      return instance;
    }

  /**
     * openDB establishes and connection and authenticates
     * with the DB server
     *
     * @return: boolean : true if successful, false otherwise
     */
    public boolean openDB()
    {
      try
      {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        dbConnection = DriverManager.getConnection(strConn);
        logFile.debug("DBManager: openDB: Connection opened");
      }
      catch (ClassNotFoundException cnfex)
      {
        logFile.error("DBManager: openDB: Error is: " +
            cnfex.getMessage());
        return false;
      }
      catch (SQLException sqlex)
      {
        logFile.error("DBManager: openDB: Error is: " +
            sqlex.getMessage());
        return false;
      }
      catch (Exception excp)
      {
        logFile.error("DBManager: openDB: Error is: " +
            excp.getMessage());
        return false;
      }
      return true;
    }

    /**
     * closeDB closes the connection with the DB
     *
     * @return: boolean : true if successful, false otherwise
     */
    public boolean closeDB()
    {
      try
      {
        if (dbConnection.isClosed() == true)
        {
          logFile.debug("DBManager: closeDB: Connection closed");
          return true;
        }
        dbConnection.close();
        logFile.debug("DBManager: closeDB: Connection closed");
      }
      catch (Exception ex)
      {
        logFile.debug("DBManager: closeDB: Could not close db " +
            "connection. Error is: " + ex.toString());
        return false;
      }
      return true;
    }

    /**
     * isConnected checks the status of the connection to the
     * DB Server
     *
     * @return: boolean : true if connected, false otherwise
     */
    public boolean isConnected()
    {
      try
      {
        return !dbConnection.isClosed();
      }
      catch(Exception e)
      {
        logFile.error("DBManager: closeDB: Could not check if " +
            "connection was connected. Error is: " + e.toString());
      }
      return false;
    }

    /**
     * getClients fetches all the clients from the DB
     *
     * @return: Client[] : null if error
     */
    public Client[] getClients()
    {
      /* Initialize variables */
      Client[] clients;
      int resultCount = 0;

      try
      {
        /* Setup the query to send to the database */
        PreparedStatement statement =
            dbConnection.prepareStatement("SELECT * FROM CLIENT");
        logFile.debug("DBManager: getClients: Query string is: " +
	      statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Check how many clients were returned in the query */
        while (resultSet.next())
        {
          resultCount++;
        }
        if (resultCount == -1)
        {
          logFile.debug("DBManager: getClients: No results.");
          return null;
        }

        /* Create client objects for each result returned */
        clients = new Client[resultCount];
        int intCounter = -1;
        resultSet.first();
        do
        {
          intCounter++;
          clients[intCounter] = new Client(resultSet.getInt(1),
                                     resultSet.getString(2),
                                     resultSet.getString(3));
        } while (resultSet.next());
        logFile.debug("DBManager: getClients: Found " +
            resultCount + " results.");

        /* Close query to database */
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.error("DBManager: getClients: Error is: " +
            exc.toString());
        return null;
      }
      return clients;
    }

    /**
     * getClientById fetches the client from the DB
     * which matches the supplied ID
     *
     * @return: Client : null if error
     */
    public Client getClientById(int clientID)
    {
      /* Initialize Variables */
      Client foundClient = null;

      try
      {
        /* Make query to database for a client */
        PreparedStatement statement = dbConnection.prepareStatement("SELECT" +
            " * from CLIENT where Client_ID=?");
        statement.setInt(1, clientID);
        logFile.debug("DBManager: getClientById: " +
            "Query string is: " + statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* if a client was found */
        if (resultSet.next())
        {
          logFile.debug("DBManager: getClientById: Found client.");
          foundClient = new Client(resultSet.getInt(1),
                            resultSet.getString(2),
                            resultSet.getString(3));
        }
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.debug("DBManager: getClientById: Error is: " +
            exc.toString());
        return null;
      }
      return foundClient;
    }

    /**
     * getClientsByName fetches all the client from the DB
     * which are similar to the supplied first or last name
     *
     * @return: Client[] : null if empty or error
     */
    public Client[] getClientsByName(String firstName, String lastName)
    {
      /* Initialize Variables */
      Client[] clients;
      PreparedStatement statement = null;

      try
      {
        /* Set up query depending on input for first and last name */
        if (0 == firstName.trim().length())
        {
          statement = dbConnection.prepareStatement("SELECT * from CLIENT" +
                          " Where LastName LIKE ?");
          statement.setString(1, lastName = "%");
        }
        else if (0 == lastName.trim().length())
        {
          statement = dbConnection.prepareStatement("SELECT * from CLIENT" +
                          " Where FirstName LIKE ?");
          statement.setString(1, firstName + "%");
        }
        else
        {
          statement = dbConnection.prepareStatement("SELECT * from CLIENT" +
                          " Where FirstName LIKE ? AND LastName LIKE ?");
          statement.setString(1, firstName + "%");
          statement.setString(2, lastName + "%");
        }

        /* Execute the query to find clients */
        int resultCount = 0;
        logFile.debug("DBManager: getClientsByName: Query string" +
            " is: " + statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Check how many results came back */
        while (resultSet.next())
        {
          resultCount++;
        }
        if (0 == resultCount)
        {
          statement.close();
          logFile.debug("DBManager: getClientsByName: No Clients" +
              " found.");
          return null;
        }

        /* Setup objects to return */
        logFile.debug("DBManager: getClientsByName: Found " +
            resultCount + " client(s).");
        clients = new Client[resultCount];
        int intCounter = -1;
        resultSet.first();
        do
        {
          intCounter++;
          clients[intCounter] = new Client(resultSet.getInt(1),
                                    resultSet.getString(2),
                                    resultSet.getString(3));
        } while (resultSet.next());
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.error("DBManager: getClientsByName: Error is: " +
            exc.toString());
        return null;
      }
      return clients;
    }

    /**
     * getWorkers fetches all the workers from the DB
     *
     *
     * @return: HomecareWorker[] : null if error
     */
    public HomecareWorker[] getWorkers()
    {
      /* Initialize Variables */
      HomecareWorker[] workers;
      int resultCount = 0;

      try
      {
        /* Setup/execute the query to retrieve all homecareworkers */
        PreparedStatement statement = dbConnection.prepareStatement("SELECT" +
            " * FROM HomecareWorker");
        logFile.debug("DBManager: getWorkers: Query String is: " +
            statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Check and see if any results were returned */
        while (resultSet.next())
        {
          resultCount++;
        }
        if (-1 == resultCount)
        {
          statement.close();
          logFile.debug("DBManager: getWorkers: No results found.");
          return null;
        }

        /* Create and return objects for results returned from query */
        workers = new HomecareWorker[resultCount];
        logFile.debug("DBManager: getWorkers: Query returned " +
            resultCount + " results.");
        int intCounter = -1;

        resultSet.first();
        do
        {
          intCounter++;
          workers[intCounter] = new HomecareWorker(resultSet.getInt(1),
                                    resultSet.getString(2),
                                    resultSet.getString(3));
        } while (resultSet.next());
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.error("DBManager: getWorkers: Error is: " +
            exc.toString());
        return null;
      }
      return workers;
    }

    /**
     * getWorkerById fetches the worker from the DB
     * which matches the supplied ID
     *
     * @return: HomecareWorker : null if error
     */
    public HomecareWorker getWorkerById(int workerID)
    {
      /* Initialize Variables */
      HomecareWorker foundHomecareWorker = null;

      try
      {
        /* Setup and execute query for homecare worker (using id) */
        PreparedStatement statement = dbConnection.prepareStatement("SELECT" +
            " * from HomecareWorker where HCW_ID=?");
        statement.setInt(1, workerID);
        logFile.debug("DBManager: getWorkerById: Query String is: " +
            statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Setup the object to return if a result was returned */
        if (resultSet.next())
        {
          logFile.debug("DBManager: getWorkerById: " +
              "Homecareworker Found.");
          foundHomecareWorker = new HomecareWorker(resultSet.getInt(1),
                                    resultSet.getString(2),
                                    resultSet.getString(3));
        }
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.debug("DBManager: getWorkerById: Error is: " +
            exc.toString());
        return null;
      }
      return foundHomecareWorker;
    }

    /**
     * getWorkersByName fetches all the workers from the DB
     * which are similar to the supplied first or last name
     *
     * @return: HomecareWorker[] : null if error
     */
    public HomecareWorker[] getWorkersByName(String firstName, String lastName)
    {
      /* Initialize Variables */
      HomecareWorker[] workers;
      PreparedStatement statement = null;

      try
      {
        /* Setup the query, depending on input for first and last name */
        if (0 == firstName.trim().length())
        {
          statement = dbConnection.prepareStatement("SELECT * from " +
                          "HOMECAREWORKER Where LastName LIKE ?");
          statement.setString(1, lastName = "%");
        }
        else if (0 == lastName.trim().length())
        {
           statement = dbConnection.prepareStatement("SELECT * from " +
                           "HOMECAREWORKER Where FirstName LIKE ?");
           statement.setString(1, firstName + "%");
        }
        else
        {
          statement = dbConnection.prepareStatement("SELECT * from " +
                          "HOMECAREWORKER Where FirstName LIKE ? AND " +
                          " LastName LIKE ?");
          statement.setString(1, firstName + "%");
          statement.setString(2, lastName + "%");
        }

        /* Execute the query */
        int resultCount = 0;
        logFile.debug("DBManager: getWorkersByName: Query " +
            "String is: " + statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Determine how many results were returned */
        while (resultSet.next())
        {
          resultCount++;
        }
        if (0 == resultCount)
        {
          logFile.debug("DBManager: getWorkersByName: No " +
              " results found.");
          statement.close();
          return null;
        }

        /* Results found, create objects from query results */
        workers = new HomecareWorker[resultCount];
        logFile.debug("DBManager: getWorkersByName: Found " +
            resultCount + " result(s).");

        int intCounter = -1;
        resultSet.first();
        do
        {
          intCounter++;
          workers[intCounter] = new HomecareWorker(resultSet.getInt(1),
                                    resultSet.getString(2),
                                    resultSet.getString(3));
        } while (resultSet.next());
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.error("DBManager: getWorkersByName: Error is: " +
            exc.toString());
        return null;
      }
      return workers;
    }

    /**
     * getWorkerSchedule fetches the schedule for the requested
     * worker during the supplied range
     *
     * @return: Schedule : null if error
     */
    public Schedule getWorkerSchedule(int workerID, Timestamp start,
                                      Timestamp end)
    {
      /* Initialize Variables */
      Appointment [] appointments = null;
      Timeslot [] timeslots = null;
      boolean isRange = true;
      logFile.debug("DBManager: getWorkerSchedule: Retrieving " +
          " worker's schedule, id = " + workerID + " between " + start +
          " and " + end + ".");

      /* Determine if schedule has a range of dates */
      if (null == start || null == end)
      {
        isRange = false;
      }

      /* Retrieve the Worker's timeslots first */
      int resultCount = 0;
      try
      {
        PreparedStatement statement = null;
        if (isRange)
        {
          statement = dbConnection.prepareStatement("SELECT * from timeslot" +
                          " Where HomecareWorker_ID=? AND " +
                          " Start >=? AND End <=?");
          statement.setInt(1, workerID);
          statement.setTimestamp(2, start);
          statement.setTimestamp(3, end);
        }
        else
        {
          statement = dbConnection.prepareStatement("SELECT * from timeslot" +
                          " Where HomecareWorker_ID=?");
          statement.setInt(1, workerID);
        }

        logFile.debug("DBManager: getWorkerSchedule: Timeslot query" +
            "string is: " + statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Count how many results were returned */
        while (resultSet.next())
        {
          resultCount++;
        }
        if (0 == resultCount)
        {
          logFile.debug("DBManager: getWorkerSchedule: No " +
              " timeslots were found.");
          statement.close();
          timeslots = null;
        }
        else
        {
          /* Create timeslot objects for each result returned from query */
          timeslots = new Timeslot[resultCount];
          logFile.debug("DBManager: getWorkerSchedule: " + resultCount +
              " results returned for timeslots.");
          int intCounter = -1;
          resultSet.first();
          do
          {
            intCounter++;
            timeslots[intCounter] = new Timeslot(resultSet.getInt(3),
                                        resultSet.getTimestamp(1),
                                        resultSet.getTimestamp(2));
          } while (resultSet.next());
          statement.close();
        }
      }
      catch (Exception exc)
      {
        logFile.error("DBManager: getWorkerSchedule: Error is: " +
            exc.toString());
        return null;
      }

      try
      {
        /* Initialize Variables */
        PreparedStatement statement = null;

        /* Retrieve the worker's appointments next */
        if (isRange)
        {
          statement = dbConnection.prepareStatement("SELECT * from " +
                          " appointment Where HomecareWorker_ID=? AND" +
                          " Start >=? AND End <=?");
          statement.setInt(1, workerID);
          statement.setTimestamp(2, start);
          statement.setTimestamp(3, end);
        }
        else
        {
          statement = dbConnection.prepareStatement("SELECT * from " +
                          " appointment Where HomecareWorker_ID=?");
          statement.setInt(1, workerID);
        }

        logFile.debug("DBManager: getWorkerSchedule: Query" +
            "string for appointments is: " + statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Count the number of results */
        resultCount = 0;
        while (resultSet.next())
        {
          resultCount++;
        }
        if (0 == resultCount)
        {
          logFile.debug("DBManager: getWorkerSchedule: No " +
              "appointments were returned.");
          statement.close();
          appointments = null;
        }
        else if (resultCount > 0)
        {
          /* Create an object to return for every query result */
          appointments = new Appointment[resultCount];
          logFile.debug("DBManager: getWorkerSchedule: " + resultCount +
              " results were returned.");

          int intCounter = -1;
          resultSet.first();
          do
          {
            intCounter++;
            appointments[intCounter] = new Appointment(resultSet.getInt(1),
                                           resultSet.getInt(4),
                                           resultSet.getInt(5),
                                           resultSet.getTimestamp(2),
                                           resultSet.getTimestamp(3));
          } while (resultSet.next());
        }
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.error("DBManager: getWorkerSchedule: Error is: " +
            exc.toString());
        return null;
      }
      /* Create schedule to return with appointments and timeslots */
      Schedule schedule = new Schedule(appointments, timeslots);
      return schedule;
    }

    /**
     * setAppointment persists an appointment between a specified
     * client and worker for a given range of time
     *
     * @return: Appointment : null if error
     */
    public Appointment setAppointment(int clientID, int workerID,
                                      Timestamp tsStart, Timestamp tsEnd)
    {
      /* Initialize Variables */
      Appointment appointment = null;

      try
      {
        /* Set up insert statement to create new appointment */
        PreparedStatement statement = dbConnection.prepareStatement("INSERT " +
                                          " INTO APPOINTMENT (startTime, " +
                                          "endTime," +
                                          " client_ID, homecareworker_ID)" +
                                          " VALUES (?, ?, ?, ?);");
        statement.setTimestamp(1, tsStart);
        statement.setTimestamp(2, tsEnd);
        statement.setInt(3, clientID);
        statement.setInt(4, workerID);
        logFile.debug("DBManager: setAppointment: Insert statement" +
            " is: " + statement.toString());
        statement.execute();
        statement.close();

        /* After executing insert, get the ID of this appointment */
        statement = dbConnection.prepareStatement("SELECT * from appointment" +
                        " Where Client_ID= ? AND " +
                        " homecareworker_ID=? AND" +
                        " StartTime=? AND " +
                        " EndTime=?");
        statement.setInt(1, clientID);
        statement.setInt(2, workerID);
        statement.setTimestamp(3, tsStart);
        statement.setTimestamp(4, tsEnd);
        ResultSet resultSet = statement.executeQuery();

        /* If results returned, insert was successful, return appointment
         * object
         */
        if (resultSet.next())
        {
          logFile.debug("DBManager: setAppointment: Insert successful!");
          appointment = new Appointment (resultSet.getInt(1),
                            resultSet.getInt(4),
                            resultSet.getInt(5),
                            resultSet.getTimestamp(2),
                            resultSet.getTimestamp(3));
        }
        else
        {
          logFile.error("DBManager: setAppointment: Insert failed!");
        }
        statement.close();
        return appointment;
      }
      catch(Exception exc)
      {
        logFile.debug("DBManager: setAppointment: Error is: " +
              exc.toString());
        return null;
      }
    }

    /**
     * deleteAppointment removes a set appointment based on the
     * appointment ID supplied
     *
     * @return: boolean : true if successful, false otherwise
     */
    public boolean deleteAppointment(Appointment appointment)
    {
      try
      {
        /* Set up statement to delete appointment */
        PreparedStatement statement = dbConnection.prepareStatement("DELETE" +
            " FROM APPOINTMENT WHERE Appt_ID=?");
        statement.setInt(1, appointment.getAppointmentID());
        logFile.debug("DBManager: deleteAppointment: Deleting " +
            "appointment.");
        statement.execute();
        statement.close();
        logFile.debug("DBManager: deleteAppointment: Appointment " +
            "deleted.");
        return true;
      }
      catch (Exception ex)
      {
        logFile.error("DBManager: deleteAppointment: Error is: " +
            ex.toString());
        return false;
      }
    }

    /**
     * getAppointmentsByClient fetches a list of appointments for
     * a specified client between a given range of time
     *
     * @return: Appointment[] : null if empty or error
     */
    public Appointment[] getAppointmentsByClient(int clientID,
                                                 Timestamp dtStart,
                                                 Timestamp dtEnd)
    {
      return getAppointmentsByID(clientID, true, dtStart, dtEnd);
    }

    /**
     * getAppointmentsByWorker fetches a list of appointments for
     * a specified worker between a given range of time
     *
     * @return: Appointment[] : null if empty or error
     */
    public Appointment[] getAppointmentsByWorker(int workerID,
                                                 Timestamp dtStart,
                                                 Timestamp dtEnd)
    {
      return getAppointmentsByID(workerID, false, dtStart, dtEnd);
    }

    /**
     * getAppointmentsByID is the worker method for getAppointmentsByClient
     * and getAppointmentsByWorker which fetches a list of appointments for
     * a specified worker between a given range of time
     *
     * @return: Appointment[] : null if empty or error
     */
    private Appointment[] getAppointmentsByID(int userID, boolean isClient,
                                              Timestamp dtStart,
                                              Timestamp dtEnd)
    {
      /* Initialize Variables */
      Appointment[] appointments;
      String columnName = isClient ? "Client_ID" : "Homecareworker_ID";
      int resultCount = 0;
      boolean isRange = true;
      if (null == dtStart || null == dtEnd)
        isRange = false;

      try
      {
        PreparedStatement statement;
        /* Get appointments for client */
        if (isRange)
        {
            statement = dbConnection.prepareStatement("SELECT *" +
                                          " from appointment Where" +
                                          " " + columnName + "=? AND " +
                                          " StartTime >=? AND " +
                                          " EndTime <=?");

            statement.setInt(1, userID);
            statement.setTimestamp(2, dtStart);
            statement.setTimestamp(3, dtEnd);
        }
        else
        {
            statement = dbConnection.prepareStatement("SELECT *" +
                                          " from appointment Where" +
                                          " " + columnName + "=?");

            statement.setInt(1, userID);
        }
        logFile.debug("DBManager: getAppointmentsByID: Query" +
            "string is: " + statement.toString());
        ResultSet resultSet = statement.executeQuery();

        /* Check how many results were returned */
        while (resultSet.next())
        {
          resultCount++;
        }
        if (-1 == resultCount || 0 == resultCount)
        {
          logFile.debug("DBManager: getAppointmentsByID: No " +
              "results returned.");
          statement.close();
          return null;
        }

        /* Create an object to return for every query result */
        appointments = new Appointment[resultCount];
        logFile.debug("DBManager: setAppointment: " + resultCount +
            " results returned.");

        int intCounter = -1;
        resultSet.first();
        do
        {
          intCounter++;
          appointments[intCounter] = new Appointment (resultSet.getInt(1),
                                         resultSet.getInt(4),
                                         resultSet.getInt(5),
                                         resultSet.getTimestamp(2),
                                         resultSet.getTimestamp(3));
        } while (resultSet.next());
        statement.close();
      }
      catch (Exception exc)
      {
        logFile.debug("DBManager: getAppointmentsByID: Error is:" +
            exc.toString());
        return null;
      }
      return appointments;
    }
}